Copyright

DataDirect Connect ODBC for XML

© 2002 DataDirect Technologies. All rights reserved.

DataDirect, DataDirect Connect, and SequeLink are registered trademarks, and Client/Server MiddleWare, DataDirect Connect ADO, DataDirect Connect Integrator, DataDirect Connect JDBC, DataDirect Connect ODBC, DataDirect Connect OLE DB, DataDirect Connect Premium, DataDirect Reflector, and DataDirect SequeLink Integrator are trademarks of DataDirect Technologies. All other trademarks are the property of their respective owners.


About Connect ODBC for XML

Connect ODBC for XML (the "XML driver") supports tabular- and hierarchical-formatted XML documents that can be accessed from either a local file system or a web server. The three main types of tabular-formatted files that the driver supports are Microsoft Data Islands, ADO 2.5 persisted files, and DataDirect Format. See Supported Tabular Formats for XML Documents for more details.

The XML driver runs in the Windows environments.

The XML driver includes a SQL Engine that provides ANSI SQL-92 support. The following table lists the SQL statements that the driver supports for the different types of file formats:

File Format	Select	Create	Insert	Update	Delete
Tabular, Microsoft Data Islands	X	X	X	X	X
Tabular, ADO 2.5 Persisted	X	X	X	X	X
Tabular, DataDirect	X	X	X	X	X
Other Tabular	X		X	X	X
Hierarchical	X
See SQL Statements Supported for more information.

See the README file shipped with your DataDirect product for the file name of the XML driver.


XML Driver Requirements
Driver Requirements

You must have Internet Explorer 5 installed.


Configuring XML Data Sources
Configuring Data Sources

To configure an XML data source, do the following:

1	Start the ODBC Administrator to display a list of data sources.

2	If you are configuring an existing data source, select the data source name and click Configure to display the ODBC XML Driver Setup dialog box.

If you are configuring a new data source, click Add to display a list of installed drivers. Select the XML driver and click Finish to display the ODBC XML Driver Setup dialog box.

3	Specify a data source name, a description (optional), and location names. To define locations, click Add. The Configure Location dialog box appears.

4	Click Apply.

5	Optionally, click the Advanced tab to configure optional data source settings. Click Apply.

6	Optionally, click the Options tab to configure advanced options. Click Apply.

7	Click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box.

8	Click OK or Cancel. If you click OK, the values you have specified become the defaults when you connect to the data source. You can change these defaults by using this procedure to reconfigure your data source. You can override the defaults by connecting to the data source using a connection string with alternate values.


Test Connection

At any point during the configuration process, you can click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box. A Logon dialog box is displayed. After providing connection information, click OK.

•	If the driver can connect, it releases the connection and displays a "connection established" message. Click OK.

•	If the driver cannot connect because of an improper environment or incorrect connection value, it will display an appropriate error message.

Verify that the all required client software is properly installed. If it is not, you will see the message:

Specified driver could not be loaded due to system error [xxx].

Click OK.


Connecting to XML Using a Logon Dialog
Connecting to a Data Source Using a Logon Dialog Box

Some ODBC applications display a logon dialog box when you are connecting to a data source. The logon dialog box is displayed for each password-protected location that you have defined for the data source.

In the logon dialog box, do the following:

1	Type your user ID and password in the appropriate fields for the Location that is displayed in the Location field.

2	Click OK to connect to the data source.


Connecting to XML Using a Connection String
Connecting to a Data Source Using a Connection String

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for XML is:

DSN=XML FILES;Create Type=ADO25

The paragraphs that follow give the names and descriptions of the attributes. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is the default.

International Sort: International Sort={0 | 1}. Determines the order that records are retrieved when you issue a Select statement with an Order By clause. If International Sort=0 (the default), the driver uses the ASCII sort order. This order sorts items alphabetically with uppercase letters preceding lowercase letters. For example, “A, b, C” would be sorted as “A, C, b.”

If International Sort=1, the driver uses the international sort order as defined by your operating system. This order is always alphabetic, regardless of case; the letters from the previous example would be sorted as “A, b, C.” See your operating system documentation concerning the sorting of accented characters.

location_name: location_name={DataDirect Closed XML ADO Provider}, where location_name is the name of the location you are defining for the connection, for example:

LOC1={DataDirect Closed XML ADO Provider}

location_name.Catalog Type Hint: location_name.Catalog Type Hint={Folder | XML Document | HTML Document}. Determines the type of location you are defining for the connection, for example:

LOC1.Catalog Type Hint=XML Document

The initial default is Folder.

location_name.Create Type: location_name.Create Type={IE5DataIsland | ADO25 | DataDirect}. Determines the style of tabular-formatted XML that is generated when a new table is created in the location specified by location_name. When set to IE5DataIsland, new tables are created with the Internet Explorer 5 Data Island XML style. When set to ADO25, new tables are created with the ADO 2.5 XML style. When set to DataDirect (the initial default), new tables are created with the DataDirect format. This format complies to the W3C recommendation for XML schema, working draft April 07, 2000.

location_name.Delete Schema: location_name.Delete Schema={0 | 1}. Determines whether externally linked schema files are deleted when a table is deleted from the location specified by location_name. The XML file for the table contains a link to this external schema file. If multiple XML files are linked to the same schema file, the schema file should not be deleted when a table is deleted. The default is 0. When set to 1, externally linked schema files are deleted when a table is deleted.

location_name.Initial Catalog: An attribute that specifies the full path for a location. The location can be either a local file system or a web server. An example for a Folder location type is:

LOC1.Initial Catalog=c:\xmlsample

location_name.Password: The password used to establish a connection to the location specified by location_name. A password is required only if the location to which you are connecting is password-protected.. If so, contact your system administrator to get your password .

location_name.Require Passwd: location_name.Require Passwd={0 | 1}. Specifies whether a User ID and password are required to establish a connection to the location specified by location_name. The default is 1, required. You must set this attribute to 1 when the location to which you want to connect is password-protected; otherwise, the connection will fail.

location_name.Resolve External: location_name.Resolve External={0 | 1}. Determines whether external references, such as DTDs, Schemas, Entities, and Notations are resolved for the XML documents contained within the location specified by location_name. The default is 1. The value 0 allows the document to be processed, even if the XML parser cannot locate the external references.

location_name.Row Hint: A string that specifies an Extensible Stylesheet Language (XSL) pattern to identify the nodes that make up the rows in the rowset of a tabular-formatted XML document contained within the location specified by location_name. See Using Hints for Tabular-Formatted XML Documents for details.

location_name.Scan Rows: Any positive integer that represents the maximum number of rows to scan during the column scan of a table in the location specified by location_name. This attribute is valid only for XML Document location types. When set to 0 (the initial default), the driver scans all rows in the table.

Limiting the number of rows to scan can reduce the amount of time it takes to determine the column information on very large documents. However, because less information is available, the determination of the data types can be incorrect.

location_name.Table Hint: A string that specifies an XSL pattern to identify the table or rowset nodes in a tabular-formatted XML document contained within the location specified by location_name. See Using Hints for Tabular-Formatted XML Documents for details.

location_name.Top Table Hint: A string that specifies an XSL pattern to identify where in a hierarchical-formatted XML document the table scan should begin, for example,
LOC1.Top Table Hint=/table/empdata. This attribute is valid only for XML Document location types.

This attribute enables you to return only the data from an XML document that is relevant to your task, thus eliminating unnecessary data from being reported.

location_name.User ID: The User ID (user name) used to establish a connection to the location specified by location_name.. A User ID is required only if the location to which you are connecting is password-protected.. If so, contact your system administrator to get your User ID.

location_name.Validate Schema: location_name.Validate Schema={0 | 1}. Determines whether the XML documents contained within the location specified by location_name are validated against their schema. The default is 0. When set to 1, the XML documents are validated against their schema. This allows a well-formed XML document to be processed, even if the document is not valid.

Log Filename: Defines the name of the file in which logging will occur. The default log file name is \Integrator.txt.

Logging: Logging={0 | 1}. Enables internal logging. The amount of logging is controlled by other attributes that must be set in the connection string. The default value is 0.

Max BLOB Compare Size: Specifies the number of bytes at the beginning of a BLOB when making comparisons in the XML driver’s evaluator. If a BLOB is larger than specified length, it is truncated before comparison.

Max BLOB Sort Size: Specifies the number of bytes at the beginning of a BLOB that are used when performing an Order By, Distinct, or Group By on a BLOB column. This option can affect in-memory indexes that are built over BLOB columns during a join. The initial default value is 512.

NOTE: Do not set the value higher than the default if the client application will use BLOBs as join condition columns.

Read Only: Read Only={0 | 1}. Can override the connection attribute to force a data source to be read-only.

Show Manufactured Schemas: Show Manufactured Schemas={0 | 1}. Determines whether the manufactured schema name is returned when a SQLTables or SQLColumns operation is performed. When set to 0 (the initial default), virtual schema names are displayed. When set to 1, manufactured schema names are displayed. See Schema Modes.

Show Virtual Schemas: Show Virtual Schemas={0 | 1}. Determines whether the virtual schema name is returned when a SQLTables or SQLColumns operation is performed. When set to 1 (the initial default), virtual schema names are displayed. See Schema Modes.

Use Floating Point Rounding: Use Floating Point Rounding={0 | 1}. Specifies whether the XML evaluator will perform a small amount of rounding when comparing floating-point numbers. The binary representation of two identical floating-point numbers can be slightly different. When enabled, the evaluator compensates for this when comparing floating-point numbers. The default value is 0.


Data Types

The data types for each supported XML format map to the standard ODBC data types:

•	Data Islands Data Types

•	ADO 2.5 Persisted Files Data Types

•	DataDirect Format Data Types


DataDirect Format Data Types

This table shows how the data types for Data Island format map to the standard ODBC data types:

DataDirect	Internal XML Name	ODBC
binary	binary	SQL_BINARY
boolean	boolean	SQL_BIT
byte	byte	SQL_TINYINT SIGNED
date	date	SQL_TYPE_DATE
decimal	decimal	SQL_NUMERIC
double	double	SQL_DOUBLE
float	float	SQL_REAL
int	int	SQL_INTEGER UNSIGNED
long	long	SQL_BIGINT SIGNED
lvarbinary	lvarbinary	SQL_LONGBINARY
short	short	SQL_SMALLINT SIGNED
time	time	SQL_TYPE_TIME
timeInstant	timeInstant	SQL_TYPE_TIMESTAMP
unsignedByte	unsignedByte	SQL_TINYINT UNSIGNED
unsignedInt	unsignedInt	SQL_INTEGER UNSIGNED
unsignedLong	unsignedLong	SQL_BIGINT UNSIGNED
unsignedShort	unsignedShort	SQL_SMALLINT UNSIGNED
varbinary	string	SQL_VARBINARY
wchar	string	SQL_CHAR
wvarchar	string	SQL_VARCHAR
wlvarchar	string	SQL_LONGVARBINARY


Data Islands Data Types

This table shows how the data types for Data Island format map to the standard ODBC data types:

XML	Internal XML Name	ODBC
binhex	bin.hex	SQL_LONGVARBINARY
boolean	boolean	SQL_BIT
currency	fixed.14.4	SQL_DECIMAL
date	date	SQL_TYPE_DATE
dateTime	dateTime	SQL_TYPE_TIMESTAMP
float	float	SQL_DOUBLE
i1	i1	SQL_TINYINT SIGNED
i2	i2	SQL_SMALLINT SIGNED
i4	i4	SQL_INTEGER SIGNED
int	int	SQL_INTEGER SIGNED
number	number	SQL_DOUBLE
r4	r4	SQL_REAL
r8	r8	SQL_DOUBLE
singleChar	singleChar	SQL_SMALLINT
string	string	SQL_LONGVARCHAR
time	time	SQL_TYPE_TIME
ui1	ui1	SQL_TINYINT SIGNED
ui2	ui2	SQL_SMALLINT SIGNED
ui4	ui4	SQL_INTEGER UNSIGNED


ADO 2.5 Persisted Files Data Types

This table shows how the data types for ADO 2.5 persisted files format map to the standard ODBC data types:

ADO 2.5 Persisted Files	Internal XML Name	ODBC
binhex	bin.hex	SQL_LONGVARBINARY
boolean	boolean	SQL_BIT
currency	fixed.14.4	SQL_DECIMAL
date	date	SQL_TYPE_DATE
dateTime	dateTime	SQL_TYPE_TIMESTAMP
float	float	SQL_DOUBLE
i1	i1	SQL_TINYINT SIGNED
i2	i2	SQL_SMALLINT SIGNED
i4	i4	SQL_INTEGER SIGNED
i8	i8	SQL_BIGINT SIGNED
int	int	SQL_INTEGER SIGNED
number	number	SQL_DOUBLE
r4	r4	SQL_REAL
r8	r8	SQL_DOUBLE
singleChar	singleChar	SQL_SMALLINT
time	time	SQL_TYPE_TIME
ui1	ui1	SQL_TINYINT SIGNED
ui2	ui2	SQL_SMALLINT SIGNED
ui4	ui4	SQL_INTEGER UNSIGNED
ui8	ui8	SQL_BIGINT UNSIGNED
wchar	string	SQL_CHAR
wvarchar	string	SQL_VARCHAR
wlvarchar	string	SQL_LONGVARBINARY


ODBC Conformance Level

The API functions supported are listed in ODBC Functions Supported, found in the General Help section of the Connect OCBC Help. In addition, the following function is supported: SQLSetPos.


Number of Connections and Statements Supported

There is no limit to the number of connections and statements supported.


Persisting a Result Set as an XML Data File

This driver allows you to persist a result set as an XML data file with embedded schema. To implement XML persistence, a client application must do the following:

1	Turn on STATIC cursors. For example:

SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_STATIC, SQL_IS_INTEGER)

NOTE: A result set can be persisted as an XML data file only if the result set is generated using STATIC cursors. Otherwise, the following error is returned:

Driver only supports XML persistence when using driver’s static cursors.

2	Execute a SQL statement. For example:

SQLExecDirect (hstmt, "Select * from GTABLE", SQL_NTS)

3	Persist the result set as an XML data file. For example:

SQLSetStmtAttr (hstmt, SQL_PERSIST_AS_XML, "c:\temp\GTABLE.XML", SQL_NTS)

NOTE: A new statement attribute is available to support XML persistence, SQL_PERSIST_AS_XML. A client application must call SQLSetStmtAttr with this new attribute as an argument. See the following table for the definition of valid arguments for SQLSetStmtAttr.

Argument	Definition
StatementHandle	The handle of the statement that contains the result set to persist as XML.
Attribute	SQL_PERSIST_AS_XML. This new statement attribute can be found in the file qesqlext.h, which is installed with the driver.
ValuePtr	Pointer to a URL that specifies the full path name of the XML data file to be generated. The directory specified in the path name must exist, and if the specified file name exists, the file will be overwritten.
StringLength	The length of the string pointed to by ValuePtr or SQL_NTS if ValuePtr points to a null terminated string.

A client application can choose to persist the data at any time that the statement is in an executed or cursor-positioned state. At any other time, the driver returns the following message:

Function Sequence Error

Using the Windows XML Persistence Demo Tool

On Windows, Connect ODBC is shipped with an XML persistence demo tool. This tool is installed in the Connect ODBC installation directory.

The tool has a graphical user interface and allows you to persist data as an XML data file. To use this tool, take the following steps:

1	From the DataDirect Technologies Connect ODBC 4.10 program group, select XML Persistence Demo. The XML Persistence dialog box appears.

2	First, you must connect to the database. Click Connect. The Select Data Source dialog box appears.

3	You must either select an existing data source or create a new one. Take one of the following actions:

•	Select an existing data source and click OK.

•	Create a new file data source by clicking New. The Create New Data Source dialog box appears. Follow the instructions in the dialog box.

•	Create a new machine data source by clicking the Machine Data Source tab and clicking New. The Create New Data Source dialog box appears. Follow the instructions in the dialog box.

4	After you have connected to a database, type a SQL Select statement in the Query text box of the XML Persistence dialog box. Then, click Persist. The Save As dialog box appears.

5	Specify a name and location for the XML data file that will be created. Then, click OK.

Note that the Status box in the XML Persistence dialog box displays whether or not the action failed or succeeded.

6	Click Disconnect to disconnect from the database.

7	Click Close to exit the tool.


General Tab, ODBC XML Driver Setup

Use the ODBC XML Driver Setup dialog box to create new XML data sources or configure existing data sources.

Data Source Name: A string that identifies this XML data source configuration in the system information. Examples include "Accounting" or "XML Files."

Description: An optional long description of a data source name. For example, "My Accounting Files" or "My XML Files in the Accounting Directory."

Location Names: The text box displays all existing location names defined for the data source your are configuring. The location names listed in the text box are used for connections according to the order that they are displayed. If you want to change the order of precedence, use the Up and Down buttons.

To define a location, click Add. The Configure Location dialog box appears.

Advanced Tab

Displays the Advanced tab, where you can configure optional data source settings, such as file caching.

Options Tab

Displays the Options tab, where you can configure advanced options, such as select method.

OK

Cancel

Apply

Test Connect


Advanced Tab, ODBC XML Driver Setup

Use the Advanced tab on the ODBC XML Driver Setup dialog box to specify optional settings when you create new XML data sources or configure existing data sources.

Logging: Select this check box to enable logging. The log file logs the SQL execution plan. By default, this check box is not selected. If you select the check box, a log file will be created in the current directory. The default log file name is \Integrator.txt.

International Sort Order: A setting to indicate the order in which records are retrieved when you issue a Select statement with an Order By clause. Clear this box to use ASCII sort order (the default setting). This order sorts items alphabetically with uppercase letters preceding lowercase letters. For example, "A, b, C" would be sorted as "A, C, b."

Select this box to use international sort order as defined by your operating system. This order is always alphabetic, regardless of case; the letters from the previous example would be sorted as "A, b, C." See your operating system documentation concerning the sorting of accented characters.

Schema Mode: From the drop-down box, select one of the schema mode options.

OK

Cancel

Apply

Test Connect


Options Tab, ODBC XML Driver Setup

Use the Options tab on the ODBC XML Driver Setup dialog box to specify optional settings when you create new XML data sources or configure existing data sources.

Driver Options: Use this field to add configuration options specific to the XML driver.

WARNING: The properties you set in the Options tab override other properties for this session only and can adversely affect the operation of the data provider. Use only authorized entries. For information about authorized entries for the Options tab, contact DataDirect Technologies technical support.

OK

Cancel

Apply

Test Connect


XML File Location

Use the Find Directory dialog box to locate to the directory where your XML files are located. After you have located the directory, select any file within the directory and click Open. Even though you are choosing a directory and not a specific file, you must select a file before you can close the Find Directory window by clicking Open.


Logon to XML Dialog
ODBC XML Driver Connection Dialog Box

This dialog box is displayed for each password-protected location that you have defined for the data source.

Location: A read-only field that displays the Location to which you are connecting.

User ID and Password: Type your user ID and password for the Location that is displayed in the Location field.

Click OK to connect to the data source.


Using Hints
Using Hints for Tabular-Formatted XML Documents

The XML driver supports table and row hints. You can specify a table hint, a row hint, or both, when configuring an XML data source or using a connection string.

Table hints should be specified so that they resolve to a single node. If a table hint resolves to a set of nodes, the first node in the set is used as the table node. The context of the table hint is always the root node of the XML document.

Row hints define the "row" element and specify whether the rowset is element-based or attribute-based. If a table hint is supplied, the context of the row node is the node to which the table hint resolves; otherwise, the context is the root node of the XML document. The column mode identifier specifies whether the columns of a row are child nodes or attributes of the row node.

When working with hints, keep in mind that the XML driver assumes that the row nodes are the immediate children of the table node.

•	If only a table hint is specified, the row nodes are the children of the node to which the hint resolves. It is assumed that all of the child nodes have the same name.

•	If only a row hint is specified, the table node is the parent of the node to which the hint resolves. If the row hint resolves to a set of nodes, the nodes in that set must all have the same parent.

•	If both a table hint and a row hint are specified, the row hint is taken to be relative to the node to which the table hint resolves.

The column mode identifier has the format:

\column mode

where mode can be one of the following options:

•	child: The columns are child nodes of the row node.

•	attr: The columns are attributes of the row node.

In the following examples, the columns are the children of the row nodes.

Example 1

Table Hint:

Row Hint: //Item

The row nodes are the nodes named Item. The table node is the parent of the row nodes. Use this form only when all of the Item nodes reside under one parent.

If some Item nodes have different parents, use a table hint or a more specific row hint to select the set of Item nodes.

Example 2

Table Hint:

Row Hint: /Bookstore/Books/Item

The row nodes are the nodes named Item. The table node is Books, which is a child of the Bookstore node.

Example 3

Table Hint: /Bookstore/Books

Row Hint:

The table node is Books, which is a child of the Bookstore node. The row nodes are the children of the Books node. It is assumed that all of the child nodes under the Books nodes have the same name. If the child nodes do not all have the same name, the name of the first child node encountered is used as the row node name. In that case, it would be better to specify both a table and row hint.

Example 4

Table Hint: /Bookstore [@location = "Raleigh"]/Books

Row Hint: ./Item

The table node is Books, which is a child of the Bookstore node. Bookstore has a "location" attribute with the value Raleigh. The row nodes are the Item nodes that are children of the Books node.

Column Mode Identifier

The following examples illustrate the use of the optional column mode identifier.

Example 5

Table Hint:

Row Hint: //Item \column attr

The row nodes are named Item. The table node is the parent of the row nodes. The columns are attributes of the row node.

Example 6

Table Hint:

Row Hint: //Item \column child

The row nodes are the nodes named Item. The table node is the parent of the row nodes. The columns are attributes of the row node.


Extensions to SQL Standards

Integrator uses SQL grammar that is compliant with entry level ANSI SQL 92. Significant extensions to the grammar are summarized in the following table.

Entry Level ANSI SQL 92 Extension	Relevant Standard or Convention
Aliasing table references	Intermediate level ANSI SQL 92
ANSI date, time, and timestamp literals	Intermediate level ANSI SQL 92
Dynamic parameter specification	Full level ANSI SQL 92
GUID literals	COM
Hex string literals	Full level ANSI SQL 92
Left outer joins	Intermediate level ANSI SQL 92
ODBC escape support	ODBC 3.0
Scalar functions	ODBC 3.0


SQL Statements Supported

The SQL Engine included with the XML driver supports the following SQL statements:

•	Select

•	Create and Drop Table

•	Delete

•	Insert

•	Update


Grammar Token Definitions

The definitions are broken into the following sections:

Regular Identifiers

Delimited Identifiers

Integer Numbers

Real Numbers

Character String Literals

GUID Literals

Hex Literals

Time and Date Literals

SQL Operators and Symbols

Keywords for the XML Driver

SQL Comments


Integrator Keywords
Keywords for the XML Driver

A keyword may not be used as a regular identifier. For example, the following statement would generate a syntax error because INDICATOR is a keyword:

SELECT INDICATOR FROM T1

You can, however, enclose a keyword in double quotation marks to form a delimited identifier. For example, the following statement is valid:

SELECT "INDICATOR" FROM T1

For a complete list of keywords, refer to the Connect ODBC Reference.


Regular Identifiers

A regular identifier must begin with a letter and may not exceed 128 characters. In addition, all ASCII characters are converted to upper case.

The following are examples of regular identifiers:

•	FOO

•	COLUMN_NAME

•	SCHEMA#NAME

•	Col3 (legal, but converted to COL3)


Delimited Identifiers

Delimited identifier tokens cannot exceed 128 characters. A double quotation character can be embedded within the string by specifying two consecutive double quotation mark characters. A delimited identifier can span multiple lines, and can contain any character except the newline character.

The following examples show delimited identifiers:

•	"$ % ^ ( $"

•	"This is a delimited variable name"


Integer Numbers

Examples of integer numbers are:

•	5

•	1004


Real Numbers

The following are examples of real numbers:

•	.10

•	12.01

•	10.

•	.01e-10

•	12E+10

•	12.01e2

•	12.01e-10

•	12.e-10


Character String Literals

Character string literals are delimited with single quotation mark characters. A single quotation character can be embedded within the string by specifying two consecutive single quotation mark characters. A character string literal can span multiple lines.

Examples are:

•	'$%^($'

•	'This is a character string literal’


SQL Operators and Symbols

The following SQL operators and symbols are identified in the grammar as single quoted strings:

Symbol	Description	Symbol	Description
':'	Colon	'<''	Less than operator
';'	Semicolon	') '	Right parenthesis
'.'	Period	'='	Equal operator
','	Comma	'+'	Plus operator
'<>'	Not equal operator	'-'	Minus operator
'<='	Less than or equal operator	'*'	Multiply operator
'>='	Greater than or equal operator	'/'	Divide operator
'>'	Greater than operator	'?'	Dynamic parameter
' ('	Left parenthesis


GUID Literals

A GUID literal uses the following format, where x is a hexadecimal digit:

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx


Hex Literals

Hex literal values are introduced with an uppercase X followed by a single quoted string of hexadecimal characters.

Examples are:

•	X’39FA’

•	X’B0F00D’


Time and Date Literals

Date, time, and timestamp literals are date, time, and timestamp values surrounded by a standard prefix and suffix.

Date literals are specified in a YYYY-MM-DD format. Time literals are specified in a HH:MM:SS format with an optional fraction component. Timestamp literals are a concatenation of date and time values.

	ODBC Syntax	ANSI SQL 92 Syntax
Date Literal	{d '1997-03-19'}	date '1997-03-19'
Time Literal	{t '11:11:11.225'}	time '11:11:11.225'
Timestamp Literal	{ts  '1997-03-19 11:11:11.225'}	timestamp '2000-03-19 11:11:11.225'
Timestamp Literal	{ts  '1997-11-19'}	timestamp '2000-11-19'


SQL Comments

ANSI SQL 92 standard comments (--) and C++ standard comments (/*...*/, //) are supported. Comments can be nested.

For example, in the following query:

SELECT col1  /* col1 comment */
/*
col2	-- col2 comment
col3	// col3 comment
col4	/* col4 comment */
*/
FROM t1

columns col2, col3, and col4 will be ignored.


XSL (Extensible Stylesheet Language)

XSL is a language for expressing stylesheets. It is made of two parts: a language for transforming XML documents, and an XML vocabulary for specifying formatting semantics.

An XSL stylesheet specifies the presentation of a class of XML documents by describing how an instance of the class is transformed into an XML document that uses the formatting vocabulary.


Apply Button

Writes the settings you have specified to the system information. These settings remain in effect until you change them in this dialog box. Clicking Cancel does not affect settings that have been applied.


OK Button

Writes the settings you have specified to the system information and closes the dialog box.


Cancel Button

Closes the dialog box without saving settings that have not been applied.


Windows

•	Windows 98

•	Windows Me

•	Windows NT with Service Pack 5 and higher

•	Windows 2000 with Service Pack 1 and higher

•	Windows XP Home Edition and Professional


Contacting Technical Support

DataDirect Technologies provides technical support for all registered users of this product, including limited installation support, for the first 30 days. You can register online for your SupportLink user ID and password, which provide access to the password-protected areas of the SupportLink web site, at http://support.datadirect-technologies.com. Select Register on the left toolbar, complete the information on the Register page, and click Submit. Your user ID and password are issued to you by email upon registration.

For support after 30 days, contact us using one of the following methods or purchase further support by enrolling in the SupportLink program. For more information about SupportLink, contact your sales representative.

The DataDirect Technologies web site provides the latest support information through SupportLink Online, our global service network that provides access to support contact details, tools, and valuable information. Our SupportLink users access information using the web and automatic email notification. SupportLink Online includes a knowledge base that allows you to search on keywords for technical bulletins and other information. You also can download product fixes and upgrades for your DataDirect products.

World Wide Web

http://support.datadirect-technologies.com

E-Mail

USA, Canada, Mexico, Australia, and Asia Pacific (except Japan)	datadirect.answerline@datadirect-technologies.com
Japan	jpn.answerline@datadirect.co.jp (beginning March 2002) dd.answerline@merant.co.jp (until March 2002)
All other countries	int.datadirect.answerline@datadirect-technologies.com
Local Telephone Support

Local phone numbers can be found at:

http://support.datadirect-technologies.com/websupport/supportnet/datadirect/
supportnetanswerline.asp

NOTE: You must have a SupportLink user ID and password to access this web site. You can register online for your SupportLink user ID and password at http://support.datadirect-technologies.com. Select Register on the left toolbar, complete the information on the Register page, and click Submit. Your user ID and password are issued immediately and confirmation is sent to you by email.

Answerline support is available 24 hours a day, seven days a week.

Fax Information

Fax US	1 919 461 4527
Fax Int.	+32 (0) 15 32 09 19

Information to Provide

When you contact us, please include this information:

•	The product serial number or a case number. If you do not have a SupportLink contract, we will ask you to speak with a sales representative.

•	Your name and organization. For a first-time call, you may be asked for full customer information, including location and contact details.

•	The version number of your DataDirect product.

•	The type and version of your operating system.

•	Any third-party software or other environment information required to understand the problem.

•	A brief description of the problem, including any error messages that you have received, and the steps preceding the occurrence of the problem. Depending on the complexity of the problem, you may be asked to submit an example so that we can recreate the problem.

•	An assessment of the severity level of the problem.


Configure Location Dialog Box, General Tab

Use the Configure Location dialog box to define a location.

Location Name: Type a name for the location you are defining, for example, Loc1.

Location Type: Select a location type. See Defining Locations for the definition of each type.

Location: Either type or select the location. See Defining Locations for valid values for each type of location.

Advanced

Displays the Advanced tab of the Configure Location dialog box, where you can specify optional additional information about the location you are defining.

OK

Cancel

Apply

Test Connect


Configure Location Dialog Box, Advanced Tab

Use the Advanced tab on the Configure Location dialog box to specify optional settings; then, click OK to return to the ODBC XML Driver Setup dialog box.

Table Hint: This field is valid only for Folder and HTML Document location types. Type a string that specifies an Extensible Sylesheet Language (XSL) pattern to identify the table or rowset nodes in an XML file. See Using Hints for Tabular-Formatted XML Documents for details.

Row Hint: This field is valid only for Folder and HTML Document location types. Type a string that specifies an XSL pattern to identify the nodes that make up the rows in the rowset. See Using Hints for Tabular-Formatted XML Documents for details.

Validate Schema: Select this check box to enable the validation of the XML document against its schema. By default, this check box is not selected. To process an XML document even if the document is not valid, clear the check box.

Resolve External References: Select this check box to enable the resolution of external references, such as DTDs, Schemas, Entities, and Notations. By default, this check box is not selected. Clearing this box allows the document to be processed, even if the XML parser cannot locate the external references.

Flush Every Change: This check box is valid only for Folder location types. Select this check box to write the document to disk after every change. By default, this check box is selected. When this check box is not selected, the driver does not write the document to disk after every insert, update, or delete operation. Clearing this check box can improve performance.

Require User ID/Password: Select this check box to specify whether a User ID and password are required to establish a connection to the location you are defining. By default, this check box is not selected. When this check box is not selected, no user ID and password are required to establish a connection to the location. You must select this check box if the location you are defining is password-protected; otherwise, the connection will fail.

User ID: Type the default user ID used to establish a connection to location you are defining. This field is enabled, but not required, when the Use Required User ID/Password check box is selected. If you do not specify a user ID and you selected the Require User ID/Password, the driver will prompt you for a User ID at connection time.

Password: Type the password used to establish a connection to the location you are defining. This field is enabled, but not required, when the Use Required User ID/Password check box is selected. If you do not specify a password and you selected the Require User ID/Password, the driver will prompt you for a password at connection time.

Table Creation: This group is valid only for Folder location types. Select one of these options to determine the style of XML that is generated when a new table is created. By default, DataDirect Format is selected.

•	Data Island Format: This option causes new tables to be created with the Internet Explorer 5 Data Island XML style.

•	ADO Format: This option causes new tables to be created with the ADO 2.5 XML style.

•	DataDirect Format: This option causes new tables to be created with the DataDirect Format (the default). This format conforms to the W3C recommendation for XML schema, working draft April 07, 2000.

Delete Linked Schema: This check box is valid only for Folder location types. Select this check box to specify whether externally linked schema files are deleted when a table is deleted. The XML document for the table contains a link to this external schema file. If multiple XML documents are linked to the same schema file, the schema file should not be deleted when a table is deleted. By default, this check box is not selected.

Max Rows to Scan: This option is valid only for XML Document location types. Type an integer that represents the maximum number of rows to scan when the XML driver is determining the data type of each column. During the scan, the driver inspects each column value in the row of a table and adjusts the data type determination for each column based on the corresponding value. The more sample column values it encounters, the more accurate the determination is.

By default, the value is 0, which means all rows in the table are scanned. Limiting the number of rows can reduce the amount of time it takes to determine the column information on very large documents. However, because less information is available, the determination of the data types can be incorrect.

OK

Cancel

Apply

Test Connect


Supported Tabular Formats for XML Documents

The three main XML tabular-formats that the XML driver can access are described in the following table. In some instances, you may need to define hints to help the XML driver read the tabular-format of an XML document correctly. See Using Hints for Tabular-Formatted XML Documents for more information.

Format	Description
ADO 2.5 persisted files	These files are identified by a unique schema namespace URL. Although ADO uses the same data types defined by XML-Data, the data types use extensions, such as adding a maximum column width for string columns. ADO 2.5 persisted files are identified by the following unique XML element:
	<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"     xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"     xmlns:rs="urn:schemas-microsoft-com:rowset"    xmlns:z="#RowsetSchema">
DataDirect Format	This XML format conforms to the W3C recommendation for XML schema, Working Draft April 07, 2000. These files are identified by the following unique XML element (schema namespace URL):
	<table xmlns=http://www.w3.org/1999/XMLSchema     xmlns:xsi=      http://www.w3.org/1999/XMLSchema-instance     xmlns:rs=      "http://www.datadirect-technologies.com/namespaces/       datadirect/xmlrecordset">
Microsoft Data Islands	These islands are identified by the <XML> tag in an HTML document. The Data Island can be embedded in the HTML document. Data Islands can include the following Schema definition and namespace:
	<Schema xmlns="urn:schemas-microsoft-com:xml-data"    xmlns:dt="urn:schemas-microsoft-com:datatypes">


Hierarchical-formatted XML File Support
Hierarchical-Formatted XML Document Support

The XML driver can be configured so that it supports hierarchical-formatted documents. In this case, the driver assumes that the document that it is accessing can contain more than one table. The driver scans the document to locate all tables; the available tables are visible through a SQLTables operation. Then, the driver does a second scan to gather each table’s column information and to determine a data type for each column.

Let’s look at an example of a hierarchical file and discuss the results.

<?xml version="1.0"?>

  <purchaseOrder orderDate="1999-10-20">

    <shipTo country="US">

      <name>Alice Smith</name>

      <street>123 Maple Street</street>

      <city>Mill Valley</city>

      <state>CA</state>

      <zip>90952</zip>

    </shipTo>

    <billTo country="US">

       <name>Robert Smith</name>

       <street>8 Oak Avenue</street>

       <city>Old Town</city>

       <state>PA</state>

       <zip>95819</zip>

    </billTo>

    <comment>Hurry, my lawn is going wild!</comment>

    <items>

       <item partNum="872-AA">

          <productName>Lawnmower</productName>

          <quantity>1</quantity>

          <USPrice>148.95</USPrice>

          <comment>Confirm this is electric</comment>

       </item>

       <item partNum="926-AA">

          <productName>Baby Monitor</productName>

          <quantity>1</quantity>

          <USPrice>39.98</USPrice>

          <shipDate>1999-05-21</shipDate>

       </item>

    </items>

  </purchaseOrder>

First, the XML driver returns two tables: “purchaseOrder” and “items.” Two tables are returned because two items are found for a single purchase order. The XML driver found commonality of child elements.

Second, the XML driver determines which columns are in a specific table. An _ID column, which is essentially a primary key, is automatically generated for each table. If a table is determined to be a child of another table, then it is given a second generated column. The name of this column is prefixed with the parent table’s name and ends with _ID, for example, _purchaseOrder_ID.

Consider the previous example file. The items table will receive two generated columns, _ID and _purchaseOrder_ID, which are assigned an integer data type. The purchaseOrder table receives only the _ID column, because it does not have a parent table.

The tables returned from the example file include the following columns:

Table	Columns
items	_ID _purchaseOrder_ID partNum productName	quantity USPrice comment shipDate
purchaseOrder	_ID orderDate shipTo_country shipTo_name shipTo_street shipTo_city shipTo_state shipTo_zip	billTo_country billTo_name billTo_street billTo_city billTo_state billTo_zip comment

Column Data Types

The XML driver determines the column data types by inspecting the column values. The data type determination limits its data types to a subset of the DataDirect Format data types, as listed in the following table. For a complete list of DataDirect Format data types, see the DataDirect Format Data Types table.

Data Type	Sample Values
wvchar	“Foo”, “best320”
varbinary	“27AB2F9C”
int	“34”,”-7000”
unsignedint	“0”, “123456789”
long	“-12345678012345”
unsignedlong	“123456789012345”
boolean	“true”, “false”
date	1963-12-19
time	10:09:58
timeinstant	1963-12-19T10:09:58
decimal	1245.678


Defining Locations

When configuring an XML data source, you must define the location of the XML or HTML documents that the driver will access. The locations can be either from a local file system or from a web server.

The types of locations are:

Folder	Implies that each XML file is a single table. When defining a Folder location, you specify only a directory as the location (not a directory and a file name), for example, C:\xmlsample.
XML Document	Implies that the full path to the XML document, including the XML file name, is the location. Using this type of location, each document can have one or more tables and can be a hierarchical-formatted XML document. When defining an XML Document location, you specify a path and an XML file name, for example, C:\xmlsample\file.xml, as the location.
HTML Document	Implies the use of an HTML document with embedded XML data islands. Using this type of location, each document can have one or more tables. When defining an HTML Document location, you specify a path and an HTML file name, for example, C:\htmlsample\file.html, as the location.


Specifying Table Names in SQL Statements

When defining locations, you specify a name for the location along with a directory, or path and file name. For example, suppose you define two locations for a data source, a Folder location and an XML Document location. The Folder location is on a local filing system and the XML Document location is on a web server with a URL prefix of http://www.acme.com/xmldata.

For example:

The Folder location:

c:\xmldata\xmlsample as LOC1

The XML Document location: http://www.acme.com/xmldata/doc.xml as LOC2

For complete information about how to configure locations in an XML data source, see Configuring Data Sources.

If you connected to this data source and the data source had the "Show Manufactured Schemas" option set as the Schema Mode and then performed an unqualified SQLTables operation, you would get the following results.

Schema name	Table name
LOC1#	FILE1
LOC1#	FILE2
LOC2#	TABLE1
LOC2#	TABLE2
Location names are fabricated into the schema name by adding a # symbol to the end of the location name.

NOTE: If you had the "Show Virtual Schemas" option set, the above table would have "XML" listed in the Schema name column.

To fully qualify a table name in a SQL statement, you could use the following example:

LOC1#.FILE1
or
XML.FILE1

LOC2#.TABLE2
or
XML.TABLE2

This design gives you a simpler table name qualifier. This is an important advantage given the complexity of URL names, and the requirement to double quote them in SQL statements. For example, the following query uses a fully qualified table name for an XML Document location:

select * from “http://www.acme.com/xmldata/doc.xml#TABLE2” where productName=’lawnmower’

Compare that to the same query using a location name:

select * from LOC2#.TABLE2 where productName=’lawnmower’

Another example demonstrating the Folder location is as follows:

select * from “c:\xmldata\xmlsample\FILE1.XML” where productName=’lawnmower’

Compare that to the same query using a location name:

select * from LOC1#.FILE1 where productName=’lawnmower’


Schema Modes

Show Virtual Schemas Only. This option returns "XML" in the Schema Name column when a SQLTables or SQLColumns operation is performed when connected to a data source. For example:

Schema Name	Table Name
XML	TAB1A
XML	TAB1B
XML	TAB2A
XML	TAB2B

Show Manufactured Schemas Only. This option returns the manufactured schema names in the Schema Name column when a SQLTables or SQLColumns operation is performed when connected to a data source. The Location names you define for a data source are manufactured into a schema name by adding a # symbol after the Location names. For example:

Schema Name	Table Name
LOC1#	TAB1A
LOC1#	TAB1B
LOC2#	TAB2A
LOC2#	TAB2B

Show Both Virtual and Manufactured Schemas. This option returns both virtual and manufactured schema names when a SQLTables or SQLColumns operation is performed when connected to a data source. For example, the information from both of the preceding tables would be returned.